﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Collections;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace BaseData
{
    /// <summary>
    /// 访问数据库的基类
    /// </summary>
    public class DataConfig : Base
    {
        /// <summary>
        /// 执行SQL，返回DataTable
        /// </summary>
        /// <param name="businessType">业务类型</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdParms">参数数组</语句>
        /// <returns>查询结果DataTable</returns>
        public static DataTable QueryRecords(BusinessType businessType, string sql, params DbParameter[] cmdParms)
        {
            string constr = GetConnectionStr(businessType);
            Database db = DatabaseFactory.CreateDatabase(constr);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            BuildDBParameter(db, dbCommand, cmdParms);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            dbCommand.Parameters.Clear();
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }

        /// <summary>
        /// 执行SQL，返回影响的记录数
        /// </summary>
        /// <param name="businessType">业务类型</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdParms">参数数组</语句>
        /// <returns>影响的记录数</returns>
        public static int QueryNoRecords(BusinessType businessType, string sql, params DbParameter[] cmdParms)
        {
            string constr = GetConnectionStr(businessType);
            Database db = DatabaseFactory.CreateDatabase(constr);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            BuildDBParameter(db, dbCommand, cmdParms);
            int records = db.ExecuteNonQuery(dbCommand);
            dbCommand.Parameters.Clear();
            return records;
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）
        /// </summary>
        /// <param name="businessType">业务类型</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdParms">参数数组</语句>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(BusinessType businessType, string sql, params DbParameter[] cmdParms)
        {
            string constr = GetConnectionStr(businessType);
            Database db = DatabaseFactory.CreateDatabase(constr);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            BuildDBParameter(db, dbCommand, cmdParms);
            object obj = db.ExecuteScalar(dbCommand);
            dbCommand.Parameters.Clear();
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                return null;
            }
            else
            {
                return obj;
            }
        }


        /// <summary>
        /// 检测一个记录是否存在
        /// </summary>
        /// <param name="businessType">业务类型</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="cmdParms">参数数组</语句>
        /// <returns>存在返回true，不存在返回false</returns>
        public static bool Exists(BusinessType businessType, string sql, params DbParameter[] cmdParms)
        {
            object obj = GetSingle(businessType, sql, cmdParms);
            int cmdresult;
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                cmdresult = 0;
            }
            else
            {
                cmdresult = int.Parse(obj.ToString());
            }
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// SQL语句的字典表（key为sql语句，value是该语句的DBParameter[]）
        /// </summary>
        /// <param name="businessType">业务类型</param>
        /// <param name="sqlList">执行多条SQL语句，实现数据库事务</param>
        public static void ExecuteSqlTran(BusinessType businessType, Dictionary<string, DbParameter[]> sqlList)
        {
            string constr = GetConnectionStr(businessType);
            Database db = DatabaseFactory.CreateDatabase(constr);
            using (DbConnection dbconn = db.CreateConnection())
            {
                dbconn.Open();
                DbTransaction dbtran = dbconn.BeginTransaction();
                try
                {
                    //执行语句
                    foreach (KeyValuePair<string, DbParameter[]> myDic in sqlList)
                    {
                        string sql = myDic.Key;
                        DbParameter[] cmdParms = myDic.Value;
                        if (sql.Trim().Length > 1)
                        {
                            DbCommand dbCommand = db.GetSqlStringCommand(sql);
                            BuildDBParameter(db, dbCommand, cmdParms);
                            db.ExecuteNonQuery(dbCommand);
                        }
                    }
                    dbtran.Commit();
                }
                catch
                {
                    dbtran.Rollback();
                }
                finally
                {
                    dbconn.Close();
                }
            }
        }

        /// <summary>
        /// 执行存储过程，返回DataTable
        /// </summary>
        /// <param name="businessType">业务类型</param>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="cmdParms">参数数组</param>
        /// <returns>查询结果DataTable</returns>
        public static DataTable QueryRecordsByStoredProcedure(BusinessType businessType, string storedProcName, params DbParameter[] cmdParms)
        {
            string constr = GetConnectionStr(businessType);
            Database db = DatabaseFactory.CreateDatabase(constr);
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
            BuildDBParameter(db, dbCommand, cmdParms);
            DataSet ds = db.ExecuteDataSet(dbCommand);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }

        /// <summary>
        /// 执行存储过程，返回影响的记录数
        /// </summary>
        /// <param name="businessType">业务类型</param>
        /// <param name="storedProcName">存储过程名称</param>
        /// <param name="cmdParms">参数数组</param>
        /// <returns>影响的记录数</returns>
        public static int QueryNoRecordsByStoredProcedure(BusinessType businessType, string storedProcName, params DbParameter[] cmdParms)
        {
            string constr = GetConnectionStr(businessType);
            Database db = DatabaseFactory.CreateDatabase(constr);
            DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
            BuildDBParameter(db, dbCommand, cmdParms);
            return db.ExecuteNonQuery(dbCommand);
        }

        /// <summary>
        /// 加载参数
        /// </summary>
        /// <param name="db">Database实例</param>
        /// <param name="dbCommand">DbCommand实例</param>
        /// <param name="cmdParms">参数数组</param>
        private static void BuildDBParameter(Database db, DbCommand dbCommand, params DbParameter[] cmdParms)
        {
            foreach (DbParameter dbParam in cmdParms)
            {
                dbCommand.Parameters.Add(dbParam);
            }
        }
    }
}
